<?php
Class NewsManage
{
    public static function getNewslist1($page, $dataPerpage, $lang, $isAdmin) {
        
        $status = $isAdmin?"'P','U'":"'P'";//ปรับสเตตัส ให้เห็นข้อมูลตามสิทธ์
        $sql = "SELECT *
            FROM news
            WHERE Status IN ({$status}) AND DeleteDate IS NULL 
            ORDER BY CreateDate DESC, NewsID DESC";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $maxPage =  ceil($command->execute()/$dataPerpage);
        
        $langUpper = strtoupper($lang);
        $startRow = $dataPerpage*($page-1);
        $sql = "SELECT *, Title{$langUpper} AS NewsTitle, Info{$langUpper} AS NewsInfo
            FROM news
            WHERE Status IN ({$status}) AND DeleteDate IS NULL 
            ORDER BY CreateDate DESC, NewsID DESC
            LIMIT {$startRow},{$dataPerpage}";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $data =  $command->queryAll();
        return array(
            'MaxPage' => $maxPage,
            'data' => $data
        );
    }

    public static function getNewsDetail($newsID){
        $sql="SELECT * FROM news WHERE NewsID = :nID;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':nID',$newsID);
        return $command->queryRow();
    }
    
    public static function getNewslist($page, $dataPerpage){
        $startRow = $dataPerpage*($page-1);
        $sql="SELECT * FROM news WHERE DeleteDate Is Null LIMIT :startRow,:dataPerpage;";
        $connection=Yii::app()->db; 
        $command=$connection->createCommand($sql);
        $command->bindParam(':startRow', $startRow);
        $command->bindParam(':dataPerpage', $dataPerpage);
        return $command->queryAll();
    }
    
    public static function updateNews($post) {
        $infoEN = addslashes(str_replace("\"","'",$post['infoEN']));
        $infoTH = addslashes(str_replace("\"","'",$post['infoTH']));
        $sql = "UPDATE news SET ";
        $sql.= "TitleEN='$post[titleEN]',";
        $sql.= "TitleTH='$post[titleTH]',";
        $sql.= "InfoEN='".$infoEN."',";
        $sql.= "InfoTH='".$infoTH."',";
        $sql.= "Status='$post[status]',";
        $sql.= "EditDate='$post[editdate]'";
        $sql.= " WHERE NewsID = :Id;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':Id', $post['newsid']);
        return $command->execute();
    }
    
    public static function InsertNews($post) {
        $infoEN = addslashes(str_replace("\"","'",$post['infoEN']));
        $infoTH = addslashes(str_replace("\"","'",$post['infoTH']));
        $sql = "INSERT INTO news(TitleEN,TitleTH,InfoEN,InfoTH,Status,CreateDate,EditDate,DeleteDate) ";
        $sql.= "VALUES('$post[titleEN]',";
        $sql.= "'$post[titleTH]',";
        $sql.= "'".$infoEN."',";
        $sql.= "'".$infoTH."',";
        $sql.= "'$post[status]',";
        $sql.= "'$post[createdate]',";
        $sql.= "'$post[editdate]',";
        $sql.= "Null);";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        return $command->execute();
    }
    
    public static function ChangeStatus($Id, $state){
        $sql = "UPDATE news SET Status = :state WHERE NewsID = :Id;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':state', $state);
        $command->bindParam(':Id', $Id);
        return $command->execute();
    }
    
    Public static function DeleteNews($Nid) {
        $sql = "UPDATE news SET DeleteDate = CURDATE() WHERE NewsID = :Nid;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':Nid',$Nid);
        return $command->execute();
    }
}
?>
